# 配置日志
import random
import re
from playwright.sync_api import Playwright, sync_playwright, expect
import os
import time
import math
import traceback
import asyncio
import uuid
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
# import seaborn as sns
from sklearn.cluster import KMeans
from sklearn.decomposition import PCA
from sklearn.preprocessing import StandardScaler
import nltk
from nltk.sentiment import SentimentIntensityAnalyzer
from nltk.tokenize import word_tokenize
from nltk.corpus import stopwords
from collections import Counter
from wordcloud import WordCloud
import sqlite3
import datetime
import pymysql
from textblob import TextBlob
import logging
import jieba
import matplotlib.font_manager as fm
from app.interface_xue.excelhelper_xue import excelhelper_xue
from datetime import datetime, timedelta
import matplotlib as mpl
from django.conf import settings

# 直接使用Django设置的媒体路径
if hasattr(settings, 'MEDIA_ROOT'):
    MEDIA_ROOT = settings.MEDIA_ROOT
else:
    # 如果无法获取Django设置，则使用相对路径
    CURRENT_DIR = os.path.dirname(os.path.abspath(__file__))
    BASE_DIR = os.path.dirname(os.path.dirname(CURRENT_DIR))
    MEDIA_ROOT = os.path.join(BASE_DIR, 'app', 'media')

# # 确保媒体目录存在
# MEDIA_REPORTS_DIR = os.path.join(MEDIA_ROOT, 'reports')
# os.makedirs(MEDIA_REPORTS_DIR, exist_ok=True)

# # 打印调试信息
# print(f"使用的媒体路径: {MEDIA_ROOT}")
# print(f"报告目录: {MEDIA_REPORTS_DIR}")

class ProductCategoryAnalyzer:
    """
    产品类别分析器 - 支持任意产品类别的亚马逊数据分析
    使用ExcelHelper类操作数据，适配修改后的MySQL字段
    """
    
    def __init__(self, category="home decor", report_dir=None, epid=None):
        """
        初始化产品分析器
        
        参数:
            category: 产品类别名称，默认为"home decor"
            report_dir: 报告保存目录，默认为category名称下的report子目录
            epid: 部门编号，用于保存分析结果时关联到特定部门
        """
        self.category = category
        self.category_slug = category.lower().replace(' ', '_')
        self.excel_helper = excelhelper_xue()  # 初始化ExcelHelper实例
        self.epid = epid  # 存储部门编号
         
        # 设置报告目录 - 直接使用media/reports目录，不再创建子目录
        # 使用时间戳防止文件名冲突
        timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
        self.report_dir =''# MEDIA_REPORTS_DIR
        
        # 日志初始化 - 使用带有时间戳的文件名
        self.log_file = os.path.join(self.report_dir, f"{self.category_slug}_{timestamp}_analysis.log")
        self.file_prefix = f"{self.category_slug}_{timestamp}_"  # 用于所有生成的文件
        self.setup_logging()
        
        # 设置中文字体
        self.set_chinese_font()
        
    def setup_logging(self):
        """设置增强的日志系统"""
        # 创建日志格式
        formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(message)s')
        
        # 设置文件处理器
        file_handler = logging.FileHandler(self.log_file, encoding='utf-8', mode='w')
        file_handler.setFormatter(formatter)
        
        # 设置控制台处理器
        console_handler = logging.StreamHandler()
        console_handler.setFormatter(formatter)
        
        # 配置根日志记录器
        self.logger = logging.getLogger(f"{self.category_slug}_analyzer")
        self.logger.setLevel(logging.INFO)
        
        # 清除可能存在的旧处理器
        if self.logger.handlers:
            self.logger.handlers = []
            
        self.logger.addHandler(file_handler)
        self.logger.addHandler(console_handler)
        
        # 记录初始信息
        self.logger.info(f"开始 {self.category} 产品分析")
        self.logger.info(f"报告将保存在: {self.report_dir}")
            
    def set_chinese_font(self):
        """设置中文字体支持"""
        try:
            # 尝试设置中文字体
            plt.rcParams['font.sans-serif'] = ['SimHei', 'Arial Unicode MS', 'Microsoft YaHei']
            plt.rcParams['axes.unicode_minus'] = False
        except:
            self.logger.warning("警告: 无法设置中文字体，图表中的中文可能显示为方块")
    
    def get_data_from_excel(self, days=30, limit=1000):
        """
        使用ExcelHelper获取产品数据
        
        参数:
            days: 获取最近多少天的数据
            limit: 最多获取多少条记录
        """
        try:
            # 计算日期范围
            end_date = datetime.now()
            start_date = end_date - timedelta(days=days)
            
            # 使用excel_helper查询数据 - 这里使用更新后的字段名
            sql = f"""
            SELECT productid AS asin, title, price, rating, reviews_count AS reviews, maincategory AS category, 
                   opdate AS date, seller, mainpic AS image_url, memo AS description
            FROM app_product_xue
            WHERE (keywords LIKE '%{self.category}%' OR title LIKE '%{self.category}%')
            AND opdate >= '{start_date.strftime('%Y-%m-%d')}'
            ORDER BY opdate DESC
            LIMIT {limit}
            """
            
            try:
                # 尝试使用ExcelHelper执行查询
                result = self.excel_helper.query_one(sql)
                
                # 检查结果是否为列表
                if not result or not isinstance(result, list):
                    self.logger.warning("查询没有返回有效数据，将使用模拟数据")
                    return self.generate_mock_data(limit)
                    
                # 将结果转换为DataFrame
                df = pd.DataFrame(result)
            except Exception as db_error:
                self.logger.warning(f"数据库查询失败: {str(db_error)}，将使用模拟数据")
                return self.generate_mock_data(limit)
            
            # 转换数据类型
            if 'price' in df.columns:
                df['price'] = pd.to_numeric(df['price'], errors='coerce')
            if 'rating' in df.columns:
                df['rating'] = pd.to_numeric(df['rating'], errors='coerce')
            if 'reviews' in df.columns:
                df['reviews'] = pd.to_numeric(df['reviews'], errors='coerce')
            
            self.logger.info(f"从数据库获取了 {len(df)} 条{self.category}产品记录")
            return df
        
        except Exception as e:
            self.logger.error(f"获取数据时出错: {str(e)}")
            # 返回模拟数据作为备份
            return self.generate_mock_data(limit)
            
    def generate_mock_data(self, count=100):
        """生成模拟数据用于测试和演示"""
        self.logger.info(f"生成{count}条模拟{self.category}产品数据")
        np.random.seed(42)  # 固定随机种子以生成可重复的结果
        
        # 创建模拟产品标题
        prefixes = ['Premium', 'Luxury', 'Modern', 'Vintage', 'Classic', 'Designer', 'Handmade']
        products = ['Vase', 'Frame', 'Clock', 'Statue', 'Candle Holder', 'Wall Art', 'Bookend', 'Lamp', 'Cushion']
        materials = ['Wooden', 'Metal', 'Glass', 'Ceramic', 'Marble', 'Fabric', 'Plastic', 'Crystal']
        styles = ['Minimalist', 'Rustic', 'Industrial', 'Scandinavian', 'Bohemian', 'Art Deco', 'Traditional']
        
        titles = []
        for _ in range(count):
            prefix = np.random.choice(prefixes)
            product = np.random.choice(products)
            material = np.random.choice(materials)
            style = np.random.choice(styles)
            titles.append(f"{prefix} {material} {product} - {style} {self.category.title()} Accent Piece")
        
        # 生成其他模拟数据
        data = {
            'asin': [f"B0{np.random.randint(10000000, 99999999)}" for _ in range(count)],
            'title': titles,
            'price': np.random.uniform(10, 200, count).round(2),
            'rating': np.random.uniform(3, 5, count).round(1),
            'reviews': np.random.randint(1, 1000, count),
            'category': [self.category] * count,
            'date': [(datetime.now() - timedelta(days=np.random.randint(1, 365))).strftime('%Y-%m-%d') for _ in range(count)],
            'seller': [f"Seller {np.random.randint(1, 50)}" for _ in range(count)],
            'description': [
                f"This {np.random.choice(styles).lower()} {np.random.choice(products).lower()} is perfect for your home. " +
                f"Made from high-quality {np.random.choice(materials).lower()} material. " + 
                f"Ideal for {self.category} enthusiasts."
                for _ in range(count)
            ]
        }
        
        df = pd.DataFrame(data)
        self.logger.info(f"成功生成 {len(df)} 条模拟数据")
        return df
    
    def analyze_price_distribution(self, df):
        """分析价格分布"""
        if df.empty or 'price' not in df.columns:
            self.logger.info("数据为空或不包含价格列，无法分析价格分布")
            return
        
        try:
            # 确保价格是数值类型
            df['price'] = pd.to_numeric(df['price'], errors='coerce')
            df = df.dropna(subset=['price'])
            
            # 基本价格统计
            price_stats = df['price'].describe()
            self.logger.info(f"\n## {self.category}价格分析")
            self.logger.info(f"价格统计:\n{price_stats.to_string()}")
            
            # 绘制价格分布直方图
            plt.figure(figsize=(10, 6))
            plt.hist(df['price'], bins=20, alpha=0.7, color='skyblue')
            plt.title(f"{self.category}产品价格分布")
            plt.xlabel("价格")
            plt.ylabel("产品数量")
            plt.grid(True, alpha=0.3)
            
            # 添加均值和中位数线
            plt.axvline(price_stats['mean'], color='red', linestyle='dashed', linewidth=1, label=f"均值: ${price_stats['mean']:.2f}")
            plt.axvline(price_stats['50%'], color='green', linestyle='dashed', linewidth=1, label=f"中位数: ${price_stats['50%']:.2f}")
            plt.legend()
            
            # 保存图表
            price_chart_path = os.path.join(self.report_dir, f"{self.file_prefix}price_distribution.png")
            plt.savefig(price_chart_path)
            plt.close()
            
            self.logger.info(f"价格分布图已保存至: {price_chart_path}")
            
            # 划分价格区间并计算各区间产品数量
            price_ranges = [0, 10, 20, 30, 50, 100, 200, float('inf')]
            labels = ['<$10', '$10-20', '$20-30', '$30-50', '$50-100', '$100-200', '>$200']
            df['price_range'] = pd.cut(df['price'], bins=price_ranges, labels=labels, right=False)
            price_range_counts = df['price_range'].value_counts().sort_index()
            
            self.logger.info(f"\n价格区间分布:\n{price_range_counts.to_string()}")
            
            # 绘制价格区间柱状图
            plt.figure(figsize=(12, 6))
            price_range_counts.plot(kind='bar', color='skyblue')
            plt.title(f"{self.category}产品价格区间分布")
            plt.xlabel("价格区间")
            plt.ylabel("产品数量")
            plt.grid(True, alpha=0.3, axis='y')
            
            # 添加数值标签
            for i, v in enumerate(price_range_counts):
                plt.text(i, v + 0.5, str(v), ha='center')
            
            # 保存图表
            price_range_chart_path = os.path.join(self.report_dir, f"{self.file_prefix}price_ranges.png")
            plt.savefig(price_range_chart_path)
            plt.close()
            
            self.logger.info(f"价格区间分布图已保存至: {price_range_chart_path}")
            
        except Exception as e:
            self.logger.error(f"分析价格分布时出错: {str(e)}")
    
    def analyze_ratings(self, df):
        """分析评分分布"""
        if df.empty or 'rating' not in df.columns:
            self.logger.info("数据为空或不包含评分列，无法分析评分分布")
            return
        
        try:
            # 移除可能的非数值评分
            df['rating'] = pd.to_numeric(df['rating'], errors='coerce')
            df_ratings = df.dropna(subset=['rating'])
            
            # 基本评分统计
            rating_stats = df_ratings['rating'].describe()
            self.logger.info(f"\n## {self.category}评分分析")
            self.logger.info(f"评分统计:\n{rating_stats.to_string()}")
            
            # 统计各评分数量
            rating_counts = df_ratings['rating'].value_counts().sort_index()
            
            # 绘制评分分布柱状图
            plt.figure(figsize=(10, 6))
            rating_counts.plot(kind='bar', color='lightgreen')
            plt.title(f"{self.category}产品评分分布")
            plt.xlabel("评分")
            plt.ylabel("产品数量")
            plt.grid(True, alpha=0.3, axis='y')
            
            # 添加数值标签
            for i, v in enumerate(rating_counts):
                plt.text(i, v + 0.5, str(v), ha='center')
            
            # 保存图表
            rating_chart_path = os.path.join(self.report_dir, f"{self.file_prefix}rating_distribution.png")
            plt.savefig(rating_chart_path)
            plt.close()
            
            self.logger.info(f"评分分布图已保存至: {rating_chart_path}")
            
            # 分析评分与价格的关系
            plt.figure(figsize=(10, 6))
            plt.scatter(df_ratings['price'], df_ratings['rating'], alpha=0.5)
            plt.title(f"{self.category}产品价格与评分关系")
            plt.xlabel("价格")
            plt.ylabel("评分")
            plt.grid(True, alpha=0.3)
            
            # 添加趋势线
            try:
                z = np.polyfit(df_ratings['price'], df_ratings['rating'], 1)
                p = np.poly1d(z)
                plt.plot(sorted(df_ratings['price']), p(sorted(df_ratings['price'])), "r--", alpha=0.8)
            except:
                pass
            
            # 保存图表
            price_rating_chart_path = os.path.join(self.report_dir, f"{self.file_prefix}price_vs_rating.png")
            plt.savefig(price_rating_chart_path)
            plt.close()
            
            self.logger.info(f"价格与评分关系图已保存至: {price_rating_chart_path}")
            
        except Exception as e:
            self.logger.error(f"分析评分分布时出错: {str(e)}")
    
    def analyze_titles(self, df):
        """分析产品标题关键词"""
        if df.empty or 'title' not in df.columns:
            self.logger.info("数据为空或不包含标题列，无法分析标题关键词")
            return
        
        try:
            # 获取所有标题
            titles = df['title'].fillna('').astype(str).tolist()
            
            # 分词处理
            all_words = []
            for title in titles:
                # 移除特殊字符
                title = re.sub(r'[^\w\s]', ' ', title)
                
                # 检测是否包含中文
                if any('\u4e00' <= ch <= '\u9fff' for ch in title):
                    # 中文分词
                    words = jieba.lcut(title)
                else:
                    # 英文分词
                    words = title.lower().split()
                
                all_words.extend(words)
            
            # 移除停用词
            english_stopwords = {'the', 'a', 'an', 'and', 'or', 'but', 'is', 'are', 'in', 'to', 'for', 'with', 'on', 'of', 'at'}
            chinese_stopwords = {'的', '了', '和', '与', '或', '是', '在', '有', '中', '也'}
            stopwords = english_stopwords.union(chinese_stopwords)
            
            filtered_words = [word for word in all_words if word.lower() not in stopwords and len(word) > 1]
            
            # 计算词频
            word_counter = Counter(filtered_words)
            top_words = word_counter.most_common(50)
            
            self.logger.info(f"\n## {self.category}产品标题关键词分析")
            self.logger.info(f"前50个关键词:\n{top_words}")
            
            # 生成词云
            wordcloud = WordCloud(
                width=1000, 
                height=600, 
                background_color='white',
                max_words=200,
                contour_width=3,
                contour_color='steelblue'
            ).generate(' '.join(filtered_words))
            
            plt.figure(figsize=(16, 10))
            plt.imshow(wordcloud, interpolation='bilinear')
            plt.axis('off')
            plt.title(f"{self.category}产品标题关键词云")
            
            # 保存词云图
            wordcloud_path = os.path.join(self.report_dir, f"{self.file_prefix}title_wordcloud.png")
            plt.savefig(wordcloud_path)
            plt.close()
            
            self.logger.info(f"标题关键词云已保存至: {wordcloud_path}")
            
            # 绘制前20个关键词柱状图
            top20_words = word_counter.most_common(20)
            words, counts = zip(*top20_words)
            
            plt.figure(figsize=(12, 8))
            plt.barh(range(len(words)), counts, align='center', color='skyblue')
            plt.yticks(range(len(words)), words)
            plt.xlabel('出现次数')
            plt.title(f"{self.category}产品标题热门关键词")
            plt.tight_layout()
            
            # 保存柱状图
            top_words_chart_path = os.path.join(self.report_dir, f"{self.file_prefix}top_keywords.png")
            plt.savefig(top_words_chart_path)
            plt.close()
            
            self.logger.info(f"热门关键词图已保存至: {top_words_chart_path}")
            
        except Exception as e:
            self.logger.error(f"分析标题关键词时出错: {str(e)}")
    
    def analyze_sentiment(self, df):
        """分析评论情感倾向"""
        if df.empty or 'description' not in df.columns:
            self.logger.info("数据为空或不包含描述列，无法进行情感分析")
            return
            
        try:
            from textblob import TextBlob
            
            descriptions = df['description'].fillna('').astype(str).tolist()
            sentiments = []
            
            # 分析每个描述的情感
            for desc in descriptions:
                if not desc or len(desc) < 10:
                    continue
                    
                # 使用TextBlob进行情感分析
                blob = TextBlob(desc)
                sentiment = blob.sentiment.polarity  # -1到1之间的值，负值表示负面情感，正值表示正面情感
                sentiments.append(sentiment)
            
            if not sentiments:
                self.logger.info("没有足够的描述数据进行情感分析")
                return
                
            # 绘制情感分布直方图
            plt.figure(figsize=(10, 6))
            plt.hist(sentiments, bins=20, alpha=0.7, color='purple')
            plt.title(f"{self.category}产品描述情感分析")
            plt.xlabel("情感倾向 (-1: 极负面, 1: 极正面)")
            plt.ylabel("数量")
            plt.grid(True, alpha=0.3)
            
            # 保存图表
            sentiment_chart_path = os.path.join(self.report_dir, f"{self.file_prefix}sentiment_analysis.png")
            plt.savefig(sentiment_chart_path)
            plt.close()
            
            self.logger.info(f"情感分析图已保存至: {sentiment_chart_path}")
            
            # 计算平均情感得分
            avg_sentiment = sum(sentiments) / len(sentiments)
            self.logger.info(f"平均情感得分: {avg_sentiment:.4f} (-1到1之间, 越高越正面)")
            
        except ImportError:
            self.logger.warning("无法进行情感分析，需要安装textblob库")
        except Exception as e:
            self.logger.error(f"进行情感分析时出错: {str(e)}")
    
    def generate_report(self, days=30, limit=1000):
        """生成完整分析报告"""
        self.logger.info(f"开始生成{self.category}产品分析报告...")
        
        # 从ExcelHelper获取数据
        df = self.get_data_from_excel(days, limit)
        
        if df.empty:
            self.logger.info("未获取到数据，报告生成失败")
            return False
        
        # 执行各项分析
        self.analyze_price_distribution(df)
        self.analyze_ratings(df)
        self.analyze_titles(df)
        self.analyze_sentiment(df)
        
        # 汇总结果
        self.logger.info(f"\n## {self.category}产品分析总结")
        
        # 记录指标值，用于保存到数据库
        market_metrics = {}
        
        if 'price' in df.columns:
            df['price'] = pd.to_numeric(df['price'], errors='coerce')
            price_mean = df['price'].mean()
            price_median = df['price'].median()
            self.logger.info(f"平均价格: ${price_mean:.2f}")
            self.logger.info(f"价格中位数: ${price_median:.2f}")
            market_metrics['price_mean'] = price_mean
            market_metrics['price_median'] = price_median
        
        if 'rating' in df.columns:
            df['rating'] = pd.to_numeric(df['rating'], errors='coerce')
            rating_mean = df['rating'].mean()
            self.logger.info(f"平均评分: {rating_mean:.2f}/5.0")
            market_metrics['rating_mean'] = rating_mean
        
        if 'reviews' in df.columns:
            df['reviews'] = pd.to_numeric(df['reviews'], errors='coerce')
            reviews_mean = df['reviews'].mean()
            self.logger.info(f"平均评论数: {reviews_mean:.0f}")
            market_metrics['reviews_mean'] = reviews_mean
        
        # 生成Excel报告
        self.generate_excel_report(df)
        
        # 将分析结果保存到ProductAnalysis表中
        try:
            from app.models import ProductAnalysis
            from django.utils import timezone
            import json
            
            # 为每个产品创建分析记录
            for index, row in df.head(10).iterrows():  # 只取前10条数据保存
                if 'productid' in row and row['productid']:
                    # 计算市场需求指数 (根据价格和评论数)
                    market_demand = 0.0
                    if 'price' in row and 'reviews' in row:
                        price_factor = min(1.0, row['price'] / (market_metrics.get('price_mean', 1) * 2))
                        review_factor = min(1.0, row['reviews'] / (market_metrics.get('reviews_mean', 1) * 2))
                        market_demand = (price_factor + review_factor) * 50  # 0-100分
                    
                    # 计算竞争程度指数 (根据类别内产品数量)
                    competition_level = min(100, len(df) / 10)
                    
                    # 计算盈利能力指数 (根据价格和评分)
                    profitability = 0.0
                    if 'price' in row and 'rating' in row:
                        price_ratio = row['price'] / market_metrics.get('price_mean', 1)
                        rating_ratio = row['rating'] / 5.0
                        profitability = min(100, (price_ratio * 0.7 + rating_ratio * 0.3) * 70)
                    
                    # 计算增长潜力指数 (基于评分和评论增长趋势)
                    growth_potential = min(100, (market_demand * 0.4 + profitability * 0.6))
                    
                    # 生成优化后的标题
                    original_title = row.get('title', '')
                    if original_title:
                        words = original_title.split()
                        if len(words) > 5:
                            # 简单的标题优化示例
                            optimized_title = ' '.join(words[:10]) + ' - ' + self.category.title()
                        else:
                            optimized_title = original_title
                    else:
                        optimized_title = f"Premium {self.category} Product"
                    
                    # 创建分析记录
                    analysis = ProductAnalysis(
                        productid=row['productid'],
                        market_demand=market_demand,
                        competition_level=competition_level,
                        profitability=profitability,
                        growth_potential=growth_potential,
                        surge_index=min(100, (market_demand + growth_potential) / 2),
                        optimized_title=optimized_title[:500],  # 限制长度
                        keyword_suggestions=json.dumps([self.category] + self.category.split()),
                        analysis_date=timezone.now(),
                        epid=self.epid  # 设置部门编号
                    )
                    analysis.save()
                    self.logger.info(f"已保存产品 {row['productid']} 的分析结果")
            
            self.logger.info("分析结果已保存到数据库")
        except Exception as e:
            self.logger.error(f"保存分析结果到数据库时出错: {str(e)}")
            import traceback
            traceback.print_exc()
        
        self.logger.info(f"\n报告生成完成，保存在: {self.report_dir}")
        return True
        
    def generate_excel_report(self, df):
        """将分析结果保存为Excel文件"""
        try:
            # 创建Excel文件路径
            excel_path = os.path.join(self.report_dir, f"{self.file_prefix}analysis.xlsx")
            
            # 创建ExcelWriter对象
            with pd.ExcelWriter(excel_path, engine='xlsxwriter') as writer:
                # 保存原始数据
                if not df.empty:
                    df.to_excel(writer, sheet_name='原始数据', index=False)
                
                # 创建统计分析工作表
                stats_df = pd.DataFrame()
                
                # 价格统计
                if 'price' in df.columns:
                    price_stats = df['price'].describe().to_frame().T
                    price_stats.index = ['价格统计']
                    stats_df = pd.concat([stats_df, price_stats])
                
                # 评分统计
                if 'rating' in df.columns:
                    rating_stats = df['rating'].describe().to_frame().T
                    rating_stats.index = ['评分统计']
                    stats_df = pd.concat([stats_df, rating_stats])
                
                # 评论数统计
                if 'reviews' in df.columns:
                    reviews_stats = df['reviews'].describe().to_frame().T
                    reviews_stats.index = ['评论数统计']
                    stats_df = pd.concat([stats_df, reviews_stats])
                
                # 保存统计数据
                if not stats_df.empty:
                    stats_df.to_excel(writer, sheet_name='统计分析')
            
            self.logger.info(f"Excel报告已保存至: {excel_path}")
            
        except Exception as e:
            self.logger.error(f"生成Excel报告时出错: {str(e)}")

# 使用示例
if __name__ == "__main__":
    # 创建分析器实例，指定产品类别
    analyzer = ProductCategoryAnalyzer(category="home decor")
    
    # 生成分析报告
    analyzer.generate_report(days=90, limit=2000)
    
    # 分析其他类别
    furniture_analyzer = ProductCategoryAnalyzer(category="furniture")
    furniture_analyzer.generate_report(days=60)
   
   